import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.email.sendEmail import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.dirUtil import *
from jsjj.util.mouseMoveUtil import *

runMode = '周模式'  # 只有日模式，因为返回的数据，都是以月数据返回的，因此不存在真正的周和月模式
# 拼多多-商家后台-数据中心-服务数据-领航员
modeName = '拼多多_商家后台_多多客服_推广计划_多多搜索'

downloadFilePath = getdownloadFilePath()


# 左边点击，直到点击到一级类目，该过程不参与循环
def SPGK(engine, conn):
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False

    sleep(6)
    pyautogui.scroll(10000)
    # 左边--【物流工具】
    viaCommonUrl('https://mms.pinduoduo.com/orders/order/carriage/list')

    # 避免反复的脏数据，因此一开始也需要进行删除的
    deleteExtendFile('*.xls')

    # 左边--【推广中心/推广计划】
    viaCommonUrl('https://mms.pinduoduo.com/exp/search/index')

    pyautogui.moveTo(1264, 217, 4)
    pyautogui.click()

    sleep(2)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')
    sleep(2)

    jsonFromWebShopName = getSjhtShopName()

    pyautogui.moveTo(1420, 534, 6)
    pyautogui.click()

    # 回到顶部
    pyautogui.scroll(1000)

    P_HT_FixGUIBug()

    # queryDailyReport
    pyautogui.moveTo(1540, 143, 4)
    pyautogui.click()

    if runMode == '日模式':
        # 点击昨天
        pyautogui.moveTo(828, 252, 3)
        for i in range(0, 6):
            # 类似排pjzc
            pyautogui.moveTo(828, 252 + 15 * i, 0.3)
            pyautogui.click()

    elif runMode == '周模式':
        pyautogui.moveTo(870, 252, 3)
        for i in range(0, 6):
            # 类似排pjzc
            pyautogui.moveTo(870, 252 + 15 * i, 0.3)
            pyautogui.click()
    elif runMode == '月模式':
        pass

    # filter textbox
    pyautogui.moveTo(1503, 169, 4)
    pyautogui.doubleClick()

    if runMode == '日模式':
        # 查询url
        clip.copy('queryHourlyReport')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴
    elif runMode == '周模式':
        # 查询url
        clip.copy('queryDailyReport')  # 先复制
        pyautogui.hotkey('ctrl', 'v')  # 再粘贴
    elif runMode == '':
        pass

    # 选中第一个文件
    pyautogui.moveTo(1522, 327, 2)
    pyautogui.click()

    # 选中json区域
    pyautogui.moveTo(1830, 492, 2)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容json内容
    sleep(2)
    value = clip.paste()
    jsonObj = json.loads(value)
    if runMode == '日模式':
        df = pd.read_json(json.dumps(jsonObj['result'][0]))
    elif runMode == '周模式':
        df = pd.read_json(json.dumps(jsonObj['result']['result']))
    elif runMode == '月模式':
        df = pd.read_json(json.dumps(jsonObj['result']['result']))

    df.rename(columns={'date': 'statDate'}, inplace=True)
    df['shopName'] = jsonFromWebShopName['result']['username']
    df['spend'] = df['spend'] / 100
    df['插入时间'] = datetime.datetime.now()
    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    if runMode == '月模式':
        df.drop(
            ['mallId', 'entityId', 'externalFieldValues', 'externalFieldValues',
             'dimensionType', 'subItems'],
            axis=1, inplace=True)
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df.drop(
            ['mallId', 'entityId', 'externalFieldValues', 'externalFieldValues',
             'dimensionType','subItems'],
            axis=1, inplace=True)
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                      datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df.drop('hourlyReportList', axis=1, inplace=True)
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']
    df.to_sql(name=modeName, con=conn, if_exists='append', index=False)

    resetGuiAtLastStepCloseF12ToTop()

    return


def deleteDuplicationData(engine):
    # 这里有特殊的逻辑，不同账号，可能有相同的类目，同一天的数据，是一模一样的，因此我们需要去除重复数据的
    # 拼多多_商家后台_交易数据
    sql = 'delete from a                                                              '
    sql += '    using ' + modeName + ' as a, ' + modeName + ' as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.statDate = b.statDate and  a.shopName = b.shopName and a.运行模式=b.运行模式 )     '
    engine.execute(sql)


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine(modeName, engine, conn, '', runMode)
        SPGK(engine, conn)

        deleteDuplicationData(engine)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    # 连接database
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
